package com.dalin.framework.dao.imple;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.sql.DataSource;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.dalin.common.PropertyConst;
import com.dalin.common.pager.PageQuery;
import com.dalin.common.pager.Paras;
import com.dalin.common.utils.StringUtils;
import com.dalin.framework.dao.DbDao;

/*****************************************************************************
 * @content: 框架级通用数据库访问接口具体hibernate实现                                                                  *
 * @author : 那建林                                                                                                                                 *
 * @crtDate: 2010-04-28                                                     *
 * @modDate: 2010-04-28                                                     *
 * @version: 1.3                                                            *
 ****************************************************************************/
public class HibDaoImple extends HibernateDaoSupport implements DbDao {
	private DataSource dataSource;
	
	public int bulkUpdate(String hql){
		try {
			return getHibernateTemplate().bulkUpdate(hql);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(
					"批量执行hql语句["+hql+"]记录错误!!!");
		}
	}
	
	public void createObj(Object entity) throws Exception {
		try {
			getHibernateTemplate().save(entity);
			getHibernateTemplate().flush();
		} catch(Exception e) {
			e.printStackTrace();
			throw new RuntimeException(
					"向数据库表中添加或更新(" + entity.getClass() + ")记录错误.");
		}
		
	}
	
	public void createOrUpdateObj(Object entity) {
		try {
			
			getHibernateTemplate().saveOrUpdate(entity);
			getHibernateTemplate().flush();
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(
					"向数据库表中添加或更新(" + entity.getClass() + ")记录错误.");
		}
	}
	
	public void createOrUpdateObjs(Collection col) {
		try {
			
			getHibernateTemplate().saveOrUpdateAll(col);
			getHibernateTemplate().flush();
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(
					"向数据库表中批量添加或更新(" + col.getClass() + ")记录错误.");
		}
	}
	
	public void deleteObj(Object entity) {
		try {
			
			getHibernateTemplate().delete(entity);
			getHibernateTemplate().flush();
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(
					"删除数据库表中("  + entity.getClass() + ")记录错误.");
		}
	}
	
	public void deleteObjs(Class<?> c, String[] delIdArray){
		try {
			
			for (String id : delIdArray) {
				getHibernateTemplate().delete(
						getHibernateTemplate().load(c, id));
			}
			getHibernateTemplate().flush();
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(
					"批量删除数据库表中("  + c.getClass() + ")记录错误.");
		}
		
	}
	
	public void deleteObjs(Class<?> c, Long[] delIdArray){
		try {
			for (Long id : delIdArray) {
				getHibernateTemplate().delete(
						getHibernateTemplate().load(c, id));
			}
			getHibernateTemplate().flush();
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(
					"批量删除数据库表中("  + c.getClass() + ")记录错误.");
		}
	}
	
	public void deleteObjs(Collection col){
		try {
			getHibernateTemplate().deleteAll(col);
			getHibernateTemplate().flush();
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(
					"批量删除数据库表中("  + col.getClass() + ")记录错误.");
		}
	}
	
	public void deleteAllObjs(Class<?> c){
		try {
			bulkUpdate("delete from " + c.getSimpleName());
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(
					"全部删除数据库表中("  + c.getClass() + ")记录错误.");
		}
	}

	public <N>N findObjById(Class<N> c, Long objId){
		try {
			return getHibernateTemplate().get(c, objId);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(
					"通过ID检索数据库表中(" + c.getClass() + ")记录错误.");
		}
	}
	
	public <N>N findObjById(Class<N> c, String objId){
		try {
			Object rtnObj = getHibernateTemplate().get(c, objId);
			return (N) (rtnObj == null ? c.newInstance() : rtnObj);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(
					"通过ID检索数据库表中(" + c.getClass() + ")记录错误.");
		}
	}
	
	@SuppressWarnings("unchecked")
	public int getTotalRowsNum(final PageQuery pageQuery){
		String hqlStr = this.getHqlStr(pageQuery);
		Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE);
		Matcher m = p.matcher(hqlStr);
		StringBuffer sb = new StringBuffer();
		while (m.find()) {
			m.appendReplacement(sb, "");
		}
		m.appendTail(sb);
		hqlStr = sb.toString();
		
		int beginPos = hqlStr.toLowerCase().indexOf("from");
		final String countHql = "select count(*) " + hqlStr.substring(beginPos);
		return (Integer) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(countHql);
				setQueryParams(query, pageQuery.getParamList(), pageQuery.getOrParamList());
				if (query.list().size() == 0) return 0;
				return Integer.parseInt(query.list().iterator().next().toString());
			}
		});
	}
	
	public List queryAllObjs(Class<?> c) {
		try {
			  return getHibernateTemplate().loadAll(c);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("全表查询[" + c.getClass() + "]错误.");
		}
	}

	public List queryMapDataBySql(String sql) throws Exception {
		StringUtils.printInfo(this.getClass(), "sql", sql);
		Connection conn = dataSource.getConnection();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		List<Map> rtnList = new ArrayList<Map>();
		Map<String, String> map = null;
		try {
			rs = stmt.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			while (rs.next()) {
				map = new HashMap<String, String>();
				for (int i = 1; i < columnCount + 1; i++) {
					map.put(rsmd.getColumnLabel(i), 
							StringUtils.toString(rs.getString(i)));
				}
				rtnList.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) rs.close();
			if (stmt != null)stmt.close();
			if (conn != null)conn.close();
		}
		return rtnList;
	}

	public List queryObjs(String hqlStr){
		return null;
	}

	@SuppressWarnings("unchecked")
	public List queryObjsToPages(final PageQuery pageQuery) {
		return  getHibernateTemplate().executeFind(new HibernateCallback() { 
			public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
				 Query query = session.createQuery(getHqlStr(pageQuery)); 
				 setQueryParams(query, pageQuery.getParamList(), pageQuery.getOrParamList());
				 if (pageQuery.getStartRow() != null
				 	 && pageQuery.getPageSize() != null) {
					 query.setFirstResult(pageQuery.getStartRow());
					 query.setMaxResults(pageQuery.getPageSize());
				 }
		         return query.list();
			} 
		}); 
	}

	public void updateObj(Object entity) {
		try {
			
			getHibernateTemplate().update(entity);
			getHibernateTemplate().flush();
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(
					"更新数据库表中(" + entity.getClass() + ")记录错误.");
		}
	}

	public List queryEqualObjs(
			Class<?> c, final String paramName, final Object paramValue){
		final StringBuffer buf = new StringBuffer();
		buf.append("select hibObj from ")
			.append(c.getSimpleName())
			.append(" hibObj where hibObj.")
		    .append(paramName)
		    .append("=:pp");
		return  getHibernateTemplate().executeFind(new HibernateCallback() { 
			public Object doInHibernate(Session session) {
				Query query = session.createQuery(buf.toString()); 
				query.setParameter("pp", paramValue);
				return  query.list(); 
			} 
		});
	}

	public List queryLikeObjs(Class<?> c, final String paramName, final String paramValue){
		// TODO Auto-generated method stub
		return null;
	}
	
	
	
	
	
	
	
	/*************************************************************************
	 * @content: 得到HQL查询语句
	 * @author : 那建林                                   
	 * @crtDate: 2009-05-07  
	 */
	private String getHqlStr(PageQuery pageQuery) {
		StringBuffer buf = new StringBuffer(pageQuery.getBaseQueryStr());
		if (!buf.toString().contains(" where ")) {
			buf.append(" where 1=1");
		}
		Paras p = null;
		int count = 0;
		for (int i = 0; i < pageQuery.getParamList().size(); i++) {
			p = (Paras) pageQuery.getParamList().get(i);
			if (isBlank(p.getPValue())) continue;
			buf.append(PropertyConst.SPACE);
			buf.append(PropertyConst.AND);
			buf.append(PropertyConst.SPACE);
			buf.append(p.getPName());
			buf.append(PropertyConst.SPACE);
			if ("in".equalsIgnoreCase(p.getPOperation().trim())) {
				buf.append("in (")
					.append(PropertyConst.COLON)
					.append(PropertyConst.HIBOBJ + count++)
					.append(")");
			} else {
				buf.append(p.getPOperation());
				buf.append(PropertyConst.COLON);
				buf.append(PropertyConst.HIBOBJ + count++);
			}
		}
		
		for (int i = 0; i < pageQuery.getOrParamList().size(); i++) {
			p = (Paras) pageQuery.getOrParamList().get(i);
			if (isBlank(p.getPValue())) continue;
			buf.append(PropertyConst.SPACE);
			buf.append(PropertyConst.OR);
			buf.append(PropertyConst.SPACE);
			buf.append(p.getPName());
			buf.append(PropertyConst.SPACE);
			buf.append(p.getPOperation());
			buf.append(PropertyConst.COLON);
			buf.append(PropertyConst.HIBOBJ + count++);
		}
		
		if (!isBlank(pageQuery.getGroupBy())) {
			buf.append(PropertyConst.GROUP_BY);
			buf.append(pageQuery.getGroupBy());
		}
		
		if (!isBlank(pageQuery.getOrderBy())) {
			buf.append(PropertyConst.ORDER_BY);
			buf.append(pageQuery.getOrderBy());
		}
		//System.out.println("hql=======" + buf.toString());
		return buf.toString();
	}
	
	
	/*************************************************************************
	 * @content: 设置Query查询参数
	 * @author : 那建林                                   
	 * @param orParamList 
	 * @crtDate: 2009-05-07  
	 */
	private void setQueryParams(Query query, List paramList, List<Paras> orParamList) {
		Paras p = null;
		int count = 0;
		for (int i = 0; i < paramList.size(); i++) {
			p = (Paras) paramList.get(i);
			if (isBlank(p.getPValue())) continue;
			if ("in".equalsIgnoreCase(p.getPOperation().trim())) {
				query.setParameterList(PropertyConst.HIBOBJ + count++, (Collection) p.getPValue());
			} else {
				query.setParameter(PropertyConst.HIBOBJ + count++, p.getPValue());
			}
			
		}
		for (int i = 0; i < orParamList.size(); i++) {
			p = (Paras) orParamList.get(i);
			if (isBlank(p.getPValue())) continue;
			query.setParameter(PropertyConst.HIBOBJ + count++, p.getPValue());
		}
	}
	private boolean isBlank(Object obj) {
		if (obj == null) return true;
		String s = obj.toString().replaceAll(PropertyConst.PERCENT, PropertyConst.EMPTY);
		if(StringUtils.isBlank(s)) return true;
		return false;
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
}